<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace app\commands;

use app\lib\Ip2Region;
use app\models\Income;
use GuzzleHttp\Client;
use yii;
use yii\console\Controller;
use yii\db\Query;

/**
 * This command echoes the first argument that you have entered.
 *
 * This command is provided as an example for you to learn how to create console commands.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
class HelloController extends Controller
{
    /**
     * This command echoes what you have entered as the message.
     * @param string $message the message to be echoed.
     */
    public function actionIndex($message = 'hello world')
    {
        echo $message . "\n";
    }

    //更新订单询盘属性
    public function actionUpdate_inquiry_property()
    {
        $sql = "UPDATE jingsourcing_inquiry a LEFT JOIN jingsourcing_customer b ON LOWER(TRIM(a.email)) = LOWER(TRIM(b.email)) SET a.order_email = b.email,
        a.customer_num = b.number";
        yii::$app->db->createCommand($sql)->execute();

        //样品单询盘里面
        $sql = "UPDATE  `jingsourcing_inquiry` a
        LEFT JOIN  `jingsourcing_income` b ON a.customer_num = b.number
        SET a.property =2
        WHERE b.property_p=18 AND a.customer_num!=''";
        yii::$app->db->createCommand($sql)->execute();

        //已下单询盘里面
        $sql = "UPDATE  `jingsourcing_inquiry` a
        LEFT JOIN  `jingsourcing_income` b ON a.customer_num = b.number
        SET a.property =3
        WHERE b.property_p=17 AND a.customer_num!='' ";
        yii::$app->db->createCommand($sql)->execute();

        //样品单客户管理
        $sql = "UPDATE  `jingsourcing_customer` a
        LEFT JOIN  `jingsourcing_income` b ON a.number = b.number
        SET a.property =2
        WHERE b.property_p=18 AND a.number!=''";
        yii::$app->db->createCommand($sql)->execute();

        //已下单客户管理
        $sql = "UPDATE  `jingsourcing_customer` a
        LEFT JOIN  `jingsourcing_income` b ON a.number = b.number
        SET a.property =3
        WHERE b.property_p=17 AND a.number!=''";
        yii::$app->db->createCommand($sql)->execute();

    }

    /**
     * 添加海运费
     */
    public function actionInsertfee()
    {
        $db   = new Query();
        $list = $db->select('*')->from(yii::$app->db->tablePrefix . 'bank_bill')->where(['type' => 3])->all();
        foreach ($list as $v) {
            $arr                    = [];
            $arr['date']            = $v['date'];
            $arr['name']            = $v['service_name'];
            $arr['customer_number'] = $v['number'];
            $arr['country']         = $v['country'];
            $arr['customer_name']   = $v['customer_name'];
            $arr['express_id']      = 8;
            $arr['invoice_order']   = $v['invocie_num'];
            $arr['remark']          = $v['product'];
            $arr['tradeno']         = $v['account'];
            $arr['fee']             = $v['out_money'];
            yii::$app->db->createCommand()->insert(yii::$app->db->tablePrefix . 'express', $arr)->execute();
        }
        echo 111;

    }

    public function actionInsert_wufoo()
    {
        //老询盘插入数据
        $url  = 'https://jingsourcing.wufoo.com/api/v3/forms/s6x7h681si9vmi/entries.json?sort=DateCreated&sortDirection=DESC&pageSize=100';
        $data = [
            'auth'   => ['43B1-GZ1X-HZQN-2UD3:', 'footastic'],
            'verify' => true,
        ];
        $client   = new Client();
        $response = (string)($client->get($url, $data)->getBody());

        //读取当前entryId;
        $sql     = 'SELECT * FROM jingsourcing_inquiry_entry WHERE id=1 LIMIT 1';
        $info    = yii::$app->db->createCommand($sql)->queryOne();
        $entryId = $tmp = $info['number'];

        $arr = json_decode($response, true);
        foreach ($arr['Entries'] as $v) {
            if ($v['EntryId'] > $entryId) {
                $tmp                      = $v['EntryId'] > $tmp ? $v['EntryId'] : $tmp;
                $arr                      = [];
                $arr['refer']             = 1;
                $arr['name']              = $v['Field1'] . ' ' . $v['Field2'];
                $arr['email']             = $v['Field3'];
                $arr['order_email']       = $v['Field3'];
                $arr['country']           = $v['Field17'];
                $arr['inquiry_detail']    = $v['Field4'];
                $arr['consultation_time'] = strtotime($v['DateCreated']);
                yii::$app->db->createCommand()->insert(yii::$app->db->tablePrefix . 'inquiry', $arr)->execute();

            }
        }
        $sql = 'UPDATE jingsourcing_inquiry_entry SET number=' . ((int) $tmp) . ' WHERE id=1';
        yii::$app->db->createCommand($sql)->execute();

        //新的询盘插入第二部分
        $sql     = "SELECT * FROM jingsourcing_inquiry_entry WHERE id=2 LIMIT 1";
        $res     = yii::$app->db->createCommand($sql)->queryOne();
        $entryId = $res['number'];
        //查询wufoo
        $client     = new Client();
        $queryParam = [
            'auth'   => ['43B1-GZ1X-HZQN-2UD3', '12121'],
            'verify' => true,
        ];
        $response = $client->get('https://jingsourcing.wufoo.com/api/v3/forms/w1gbdw0k0gdnov1/entries.json?system=ip&sort=DateCreated&sortDirection=DESC&pageSize=100', $queryParam);

        $list       = (string) $response->getBody();
        $list       = json_decode($list, true);
        $maxEntryId = $list['Entries'][0]['EntryId'];
        $ipsearch   = new Ip2Region(dirname(__DIR__) . '/ip2region.db');
        foreach ($list['Entries'] as $key => $v) {
            if ((int) $v['EntryId'] > $entryId) {
                $arr                      = [];
                $arr['refer']             = 1;
                $arr['name']              = $v['Field1'] . ' ' . $v['Field2'];
                $arr['email']             = $v['Field3'];
                $arr['order_email']       = $v['Field3'];
                $arr['inquiry_detail']    = $v['Field4'];
                $arr['consultation_time'] = strtotime($v['DateCreated']);
                $city                     = $ipsearch->memorySearch($v['IP']);
                $arr['country']           = $city['region'];
                yii::$app->db->createCommand()->insert(yii::$app->db->tablePrefix . 'inquiry', $arr)->execute();
            }
        }
        if ($maxEntryId > $entryId) {
            $sql = 'UPDATE jingsourcing_inquiry_entry SET number=' . ((int) $maxEntryId) . ' WHERE id=2';
            yii::$app->db->createCommand($sql)->execute();
        }

    }

    public function actionUpdateOrderTimes()
    {
        $sql = "UPDATE jingsourcing_customer a LEFT JOIN (
              SELECT COUNT( * ) AS num, customer_num
              FROM jingsourcing_invoice_new
              GROUP BY customer_num
              )b ON a.number = b.customer_num
              SET a.order_times = b.num";
        yii::$app->db->createCommand($sql)->execute();
    }

    public function actionUpdateInvoiceRecommend()
    {
        $sql = "UPDATE jingsourcing_invoice_new a
              LEFT JOIN jingsourcing_customer b
              ON a.customer_num=b.number
              SET a.recommend=b.refer,a.new_recommend=b.new_recommend
              WHERE a.date>='" . date('Y-m') . "-01'";
        yii::$app->db->createCommand($sql)->execute();
    }

    public function actionUpdateRealIncome()
    {
        $sql = "UPDATE `jingsourcing_invoice_new` a
        LEFT JOIN  (
        SELECT SUM(in_come+fee+total_fee) AS in_come,invocie_num
        FROM `jingsourcing_income` WHERE invocie_num!=''
        GROUP BY invocie_num) AS b ON a.invoice=b.invocie_num
        SET a.real_income=b.in_come";
        yii::$app->db->createCommand($sql)->execute();
    }

    /**
     *更新采购合同总费用
     */
    public function actionUpdatePurchaseFee()
    {
        $db   = new Query();
        $list = $db->select('*')
            ->from(yii::$app->db->tablePrefix . 'purchase')
            ->all();
        foreach ($list as $k => $v) {
            $productList = json_decode($v['product_list'], true);
            $totalFee    = 0;
            if ($v['ship_fee_show']) {
                $totalFee += $v['ship_fee'];
            }
            foreach ($productList as $v2) {
                $totalFee += $v2['total'];
            }

            if ($totalFee != $v['total_fee']) {
                yii::$app->db->createCommand()->update(yii::$app->db->tablePrefix . 'purchase', ['total_fee' => $totalFee], ['id' => $v['id']])->execute();
            }
        }
    }

    /**
     *invoice所有详细数据
     **/
    public function actionInsetInvoiceDetail()
    {
        $db   = new Query();
        $list = $db->select('a.invoice,b.*')
            ->from(yii::$app->db->tablePrefix . 'invoice_new' . ' as a')
            ->join('left join', yii::$app->db->tablePrefix . 'invoice' . ' as b', 'a.invoice=b.invoice_id')
            ->where(['>=', 'a.date', '2017-07-31'])
            ->andWhere(['a.discount_status' => 2])
            ->andWhere(['b.pid' => 0])
            ->all();
        foreach ($list as $k => $v) {
            $productList  = json_decode($v['product_list'], true);
            $productMoney = 0;
            $serviceFee   = 0;
            if (!empty($productList)) {
                foreach ($productList as $v2) {
                    if (isset($v2['type'])) {
                        if ($v2['type'] == 1) {
                            $productMoney += $v2['subtotal'];
                        }
                        if ($v2['type'] == 3) {
                            $serviceFee += $v2['subtotal'];
                        }
                    }
                }
            }
            $service = json_decode($v['service'], true);
            if ($service['show'] == "true") {
                $serviceFee += (float) $service['fee'];
            }
            yii::$app->db->createCommand()->update(yii::$app->db->tablePrefix . 'invoice_new', ['product_fee' => $productMoney, 'service_fee' => $serviceFee], ['invoice' => $v['invoice']])->execute();
        }
    }

    /**更新报销fee
     * @throws yii\db\Exception
     */
    public function actionUpdateBaoxiao()
    {
        $db   = new Query();
        $list = $db->select('*')->from(yii::$app->db->tablePrefix . 'baoxiao')->all();
        foreach ($list as $v) {
            $info       = json_decode($v['detail'], true);
            $tmp        = [];
            $tmp['fee'] = $info['fee'];
            yii::$app->db->createCommand()->update(yii::$app->db->tablePrefix . 'baoxiao', $tmp, ['id' => $v['id']])->execute();
        }
    }

    public function actionUpdateQuoteProduct()
    {
        $db   = new Query();
        $list = $db->select('*')->from('jingsourcing_quote')->all();
        foreach ($list as $v) {
            foreach (json_decode($v['product_list'], true) as $v2) {
                $tmp                 = $v2;
                $tmp['product_name'] = $v2['item'];
                $tmp['job_number']   = $v['job_number'];
                $tmp['date']         = $v['date'];
                $tmp['img_src']      = !empty($v2['img_src']) ? $v2['img_src'][0] : '';
                if (!$tmp['pcs']) {
                    $tmp['pcs'] = 1;
                }
                if (empty($tmp['unit'])) {
                    $tmp['unit'] = 'pcs';
                }
                if (empty($tmp['price'])) {
                    $tmp['price'] = 0;
                }
                unset($tmp['item']);
                unset($tmp['extra_fee']);
                unset($tmp['subtotal']);
                yii::$app->db->createCommand()->insert('jingsourcing_quote_product', $tmp)->execute();
            }
        }
    }

    public function actionUpdateIncome()
    {
        $db   = new Query();
        $list = $db->select('*')->from('jingsourcing_income')->where('pid=id')
            ->andWhere(['in', 'pay_from', [1]])
            ->andWhere(['>=', 'date', '2018-03-31'])
            ->orderBy(['date' => SORT_ASC, 'id' => SORT_ASC])
            ->all();
//        $model = new AccountMoney();
        //        $money = $model->all();
        $model  = new Income();
        $remain = $list[0]['remain_money'];
        foreach ($list as $k => $v) {
            if ($k >= 1) {
                if ($v['pay_from'] > 0) {
                    $remain += ($v['in_come'] + $v['total_fee'] + $v['fee']);
                    $model->save(['remain_money' => $remain], ['id' => $v['id']]);
                }
            }

        }

        echo 'success';
    }

    /**
     *添加装箱信息
     *
     */
    public function actionAddContainerInfo()
    {
        $sql  = 'SELECT a.id,a.purchase_num,a.product_status,a.product_list,a.create_at,a.job_number FROM `jingsourcing_purchase` a  left join jingsourcing_staff b on a.job_number=b.job_number where b.group_id=4 ORDER BY `create_at` ASC';
        $list = yii::$app->db->createCommand($sql)->queryAll();
        foreach ($list as $key => $value) {
            $arr = json_decode($value['product_list'], true);
            foreach ($arr as $k => $v) {
                $data                 = [];
                $data['staff']        = $value['job_number'];
                $data['purchase_num'] = $value['purchase_num'];
                $data['name']         = $v['item'];
                $data['num']          = $v['num'];
                $data['price']        = $v['price'];
                $data['pid']          = $value['id'];
                $data['status']       = $value['product_status'];
                $data['create_at']    = strtotime($value['create_at']);
                yii::$app->db->createCommand()->insert('jingsourcing_container_product', $data)->execute();
            }
        }
    }
}
